﻿using System;
using System.Configuration;
using System.Data;
using System.Data.SqlClient;

namespace DAL
{
    public class clsConnect
    {
        SqlCommand cmd;

        #region[ConnectionString]
        public static string ConnectionString()
        {
            var _Server = ConfigurationManager.AppSettings["server"];
            var _DBName = ConfigurationManager.AppSettings["dbname"];
            var _UserName = ConfigurationManager.AppSettings["username"];
            var _PassWord = ConfigurationManager.AppSettings["password"];
            var strConn = @"Server=" + _Server + ";"
                          + "Database=" + _DBName + ";"
                          + "user=" + _UserName + ";"
                          + "password=" + _PassWord + ";"
                          + "MultipleActiveResultSets=True; Pooling = 'true'; Max Pool Size = 10000;";
            if (String.IsNullOrEmpty(_Server))
            {
                throw new Exception("Server null!");
            }
            if (String.IsNullOrEmpty(_DBName))
            {
                throw new Exception("Database Name null!");
            }
            if (String.IsNullOrEmpty(_UserName))
            {
                throw new Exception("User Name null!");
            }
            if (String.IsNullOrEmpty(_PassWord))
            {
                throw new Exception("Password Name null!");
            }
            return strConn;
        }
        #endregion

        #region[Create-SqlCommand]
        public void CreateNewSqlCommand()
        {
            cmd = new SqlCommand();
        }
        public void AddParameter(string parameterName, object value)
        {
            var parameter = new SqlParameter
            {
                ParameterName = parameterName,
                Value = value
            };
            cmd.Parameters.Add(parameter);
        }
        #endregion

        #region[GetData]
        public DataTable GetData(string storepoceduce)
        {
            try
            {
                var dataTable = new DataTable();
                using (var connection = new SqlConnection(ConnectionString()))
                {
                    cmd.Connection = connection;
                    cmd.CommandText = storepoceduce;
                    cmd.CommandType = CommandType.StoredProcedure;
                    using (var adapter = new SqlDataAdapter(cmd))
                    {
                        connection.Open();
                        adapter.Fill(dataTable);
                    }
                }

                return dataTable;
            }
            catch (SqlException ex)
            {
                if (ex.Number.ToString() == "53")
                {
                    throw new Exception("Server invalid!");
                }
                if (ex.Number.ToString() == "4060")
                {
                    throw new Exception("Database invalid!");
                }
                if (ex.Number.ToString() == "18456")
                {
                    throw new Exception("Username or password invalid!");
                }
                throw new Exception(ex.Message);
            }
        }
        #endregion

        #region[Execute]
        public bool ExecuteNonQuery(string storepoceduce)
        {
            try
            {
                using (var connection = new SqlConnection(ConnectionString()))
                {
                    cmd.Connection = connection;
                    cmd.CommandText = storepoceduce;
                    cmd.CommandType = CommandType.StoredProcedure;
                    connection.Open();
                    return cmd.ExecuteNonQuery() != 0;
                }
            }
            catch (SqlException ex)
            {
                if (ex.Number.ToString() == "53")
                {
                    throw new Exception("Server invalid!");
                }
                if (ex.Number.ToString() == "4060")
                {
                    throw new Exception("Database invalid!");
                }
                if (ex.Number.ToString() == "18456")
                {
                    throw new Exception("Username or password invalid!");
                }
                throw new Exception(ex.Message);
            }
        }
        public int ExecuteNonQueryOutput(string sProcName, string Msg)
        {
            try
            {
                using (var connection = new SqlConnection(ConnectionString()))
                {
                    cmd.Connection = connection;
                    cmd.CommandText = sProcName;
                    cmd.CommandType = CommandType.StoredProcedure;
                    cmd.Parameters.Add(Msg, SqlDbType.Int).Direction = ParameterDirection.Output;
                    connection.Open();
                    cmd.ExecuteNonQuery();
                }
            }
            catch (SqlException ex)
            {
                if (ex.Number.ToString() == "53")
                {
                    throw new Exception("Server invalid!");
                }
                if (ex.Number.ToString() == "4060")
                {
                    throw new Exception("Database invalid!");
                }
                if (ex.Number.ToString() == "18456")
                {
                    throw new Exception("Username or password invalid!");
                }
                throw new Exception(ex.Message);
            }
            return int.Parse(cmd.Parameters[Msg].Value.ToString());
        }
        public int ExecuteNonQueryReturnResult(string sProcName, string ReferenceID)
        {
            cmd.CommandText = sProcName;
            try
            {
                using (var connection = new SqlConnection(ConnectionString()))
                {
                    cmd.Connection = connection;
                    cmd.CommandText = sProcName;
                    cmd.CommandType = CommandType.StoredProcedure;
                    cmd.Parameters.Add(ReferenceID, SqlDbType.Int).Direction = ParameterDirection.ReturnValue;
                    connection.Open();
                    cmd.ExecuteNonQuery();
                }
                
            }
            catch (SqlException ex)
            {
                if (ex.Number.ToString() == "53")
                {
                    throw new Exception("Server invalid!");
                }
                if (ex.Number.ToString() == "4060")
                {
                    throw new Exception("Database invalid!");
                }
                if (ex.Number.ToString() == "18456")
                {
                    throw new Exception("Username or password invalid!");
                }
                throw new Exception(ex.Message);
            }
            return int.Parse(cmd.Parameters[ReferenceID].Value.ToString());
        }
        #endregion
    }
}